#region Includes

using System;
using System.Data;
using System.Runtime.InteropServices;
using System.Threading;

#endregion

namespace ArkeSystems.BO
{
	internal class Sqlite3
	{
		#region Private Calls
		
		[DllImport("sqlite3.dll")]
		protected static extern int sqlite3_open(string sFileName, ref IntPtr ptrNewDBHandle);

		[DllImport("sqlite3.dll")]
		protected static extern int sqlite3_close(IntPtr ptrDBHandle);

		[DllImport("sqlite3.dll")]
		protected static extern int sqlite3_exec(IntPtr ptrDBHandle, string sSQL, int iZero1, int iZero2, out IntPtr ptrErrMsg); 

		[DllImport("sqlite3.dll")]
		protected static extern int sqlite3_prepare(IntPtr ptrDBHandle, string sSQL, int iZero4, ref IntPtr ptrCompiledStatement, ref IntPtr ptrTail);

		[DllImport("sqlite3.dll")]
		protected static extern int sqlite3_step(IntPtr ptrDBHandle);

		[DllImport("sqlite3.dll")]
		protected static extern int sqlite3_reset(IntPtr ptrDBHandle);

		[DllImport("sqlite3.dll")]
		protected static extern int sqlite3_column_count(IntPtr ptrDBHandle);

		[DllImport("sqlite3.dll")]
		protected static extern int sqlite3_column_type(IntPtr ptrDBHandle, int iColumn);

		[DllImport("sqlite3.dll")]
		protected static extern string sqlite3_column_name(IntPtr ptrDBHandle, int iColumn);

		[DllImport("sqlite3.dll")]
		protected static extern int sqlite3_column_int(IntPtr ptrDBHandle, int iColumn);

		[DllImport("sqlite3.dll")]
		protected static extern string sqlite3_column_text(IntPtr ptrDBHandle, int iColumn);

		[DllImport("sqlite3.dll")]
		protected static extern int sqlite3_finalize(IntPtr ptrDBHandle);

		[DllImport("sqlite3.dll")]
		protected static extern int sqlite3_busy_timeout(IntPtr ptrDBHandle, int milliSeconds);

		[DllImport("sqlite3.dll")]
		protected static extern int sqlite3_interrupt(IntPtr ptrDBHandle);
		#endregion
		
		#region Constant members		
		protected const int SQLITE_OK         =   0;   /* Successful result */
		protected const int SQLITE_ERROR      =   1;   /* SQL error or missing database */
		protected const int SQLITE_INTERNAL   =   2;   /* An internal logic error in SQLite */
		protected const int SQLITE_PERM       =   3;   /* Access permission denied */
		protected const int SQLITE_ABORT      =   4;   /* Callback routine requested an abort */
		protected const int SQLITE_BUSY       =   5;   /* The database file is locked */
		protected const int SQLITE_LOCKED     =   6;   /* A table in the database is locked */
		protected const int SQLITE_NOMEM      =   7;   /* A malloc() failed */
		protected const int SQLITE_READONLY   =   8;   /* Attempt to write a readonly database */
		protected const int SQLITE_INTERRUPT  =   9;   /* Operation terminated by sqlite_interrupt() */
		protected const int SQLITE_IOERR      =  10;   /* Some kind of disk I/O error occurred */
		protected const int SQLITE_CORRUPT    =  11;   /* The database disk image is malformed */
		protected const int SQLITE_NOTFOUND   =  12;   /* (Internal Only) Table or record not found */
		protected const int SQLITE_FULL       =  13;   /* Insertion failed because database is full */
		protected const int SQLITE_CANTOPEN   =  14;   /* Unable to open the database file */
		protected const int SQLITE_PROTOCOL   =  15;   /* Database lock protocol error */
		protected const int SQLITE_EMPTY      =  16;   /* (Internal Only) Database table is empty */
		protected const int SQLITE_SCHEMA     =  17;   /* The database schema changed */
		protected const int SQLITE_TOOBIG     =  18;   /* Too much data for one row of a table */
		protected const int SQLITE_CONSTRAINT =  19;   /* Abort due to constraint violation */
		protected const int SQLITE_MISMATCH   =  20;   /* Data type mismatch */
		protected const int SQLITE_MISUSE     =  21;   /* Library used incorrectly */
		protected const int SQLITE_NOLFS      =  22;   /* Uses OS features not supported on host */
		protected const int SQLITE_AUTH       =  23;   /* Authorization denied */
		protected const int SQLITE_ROW        = 100;   /* sqlite_step() has another row ready */
		protected const int SQLITE_DONE       = 101;   /* sqlite_step() has finished executing */

		protected const int SQLITE_INTEGER    = 1;
		protected const int SQLITE_FLOAT      = 2;
		protected const int SQLITE_TEXT       = 3;
		protected const int SQLITE_BLOB       = 4;
		protected const int SQLITE_NULL       = 5;

		protected	const	int			BUSYRETRIES				=	5;
		protected	const	int			BUSYDELAY				=	5;
		protected	static	bool		LOCKED					=	false;
		public		static	IntPtr		MainDBHandle			=	IntPtr.Zero;
		private		static	IntPtr		ptrCompiledStatement	=	IntPtr.Zero;

		#endregion
		
		#region Public Methods
		
		// Open Database, Create if needed
		public static bool OpenDatabase(string dbName)
		{
			int localResult;
			localResult = sqlite3_open(dbName, ref MainDBHandle);
			localResult = sqlite3_busy_timeout(MainDBHandle, 2500);

			if ( localResult == SQLITE_OK )
				return true;
			else
				return false;
		}

		// Open Database, Create if needed
		public static bool OpenDatabase(string dbName, ref IntPtr DBHandle)
		{
			int localResult;
			localResult = sqlite3_open(dbName, ref DBHandle);
			localResult = sqlite3_busy_timeout(DBHandle, 2500);

			if ( localResult == SQLITE_OK )
				return true;
			else
				return false;
		}

		// Close Database
		public static void CloseDatabase()
		{
			int localResult;
			localResult = sqlite3_close(MainDBHandle);
		}

		// Execute All Non-Returnable Queries
		public static bool ExecuteNonQuery(string dbStatement)
		{
			while (LOCKED)
				Thread.Sleep(Sqlite3.BUSYDELAY);

			LOCKED = true;

			int			localResult;
			int			retries = 0;				
					
			try
			{				
				IntPtr	ptrErrMsg = IntPtr.Zero;

				if (!MainDBHandle.Equals(IntPtr.Zero))
				{
					while ( (localResult = sqlite3_exec(MainDBHandle, dbStatement, 0, 0, out ptrErrMsg)) == Sqlite3.SQLITE_BUSY && retries < Sqlite3.BUSYRETRIES) 
					{
						Thread.Sleep(Sqlite3.BUSYDELAY);
						retries++;
					}

					string errMsg;
					if (!ptrErrMsg.Equals(IntPtr.Zero))
					{
						errMsg = Marshal.PtrToStringAnsi (ptrErrMsg);
						Console.WriteLine("----------------------------------------");
						Console.WriteLine("DBStatement:" + dbStatement);
						Console.WriteLine(errMsg.ToUpper() + " : ErrorNumber : " + localResult);
					}

					if ( localResult == SQLITE_OK )
						return true;
					else
						return false;
				}

				return false;
			}
			catch(Exception e)
			{
				Console.WriteLine("error" + e.ToString());
				sqlite3_interrupt(MainDBHandle);
				return false;
			}
			finally
			{
				LOCKED = false;
			}				
		}

		public static void InterruptQuery()
		{
			if (!MainDBHandle.Equals(IntPtr.Zero))
				sqlite3_interrupt(MainDBHandle);

			if (!ptrCompiledStatement.Equals(IntPtr.Zero))
				sqlite3_finalize(ptrCompiledStatement);
		}


		// Execute Queries that return data (SELECT)
		public static DataSet ExecuteQuery(string dbStatement)
		{
			while (LOCKED)
				Thread.Sleep(Sqlite3.BUSYDELAY);

			LOCKED = true;

			DataSet		objDSet		= new DataSet();
			DataTable	NewTable	= objDSet.Tables.Add("tblNew");
			string		ColumnName;	
			int			localResult;
			ptrCompiledStatement = IntPtr.Zero;								

			try
			{					
				if (!MainDBHandle.Equals(IntPtr.Zero))
				{
					IntPtr ptrTail;
					int iRows;
					int iColumns;
					int iType;				
					bool ColumnAdded;
				
					ptrTail = IntPtr.Zero;
					ColumnAdded = false;
			
					localResult = sqlite3_prepare(MainDBHandle, dbStatement, dbStatement.Length, ref ptrCompiledStatement, ref ptrTail);
			
					iColumns = sqlite3_column_count(ptrCompiledStatement);

					if ( iColumns > 0 )
					{
						iRows = 0;

						for ( int iCCounter = 0; iCCounter < iColumns; iCCounter++ )
						{
							try 
							{
								ColumnName = sqlite3_column_name(ptrCompiledStatement, iCCounter);	
							} 
							catch
							{
								ColumnName = "bad_" + iCCounter.ToString();
							}

							if (ColumnAdded != true)
							{							
								NewTable.Columns.Add(ColumnName);								
							}
						}
						
						while ( sqlite3_step(ptrCompiledStatement) != SQLITE_DONE )
						{
							iRows++;

							DataRow	objRow = NewTable.NewRow();
				
							for ( int iCounter = 0; iCounter < iColumns; iCounter++ )
							{
								iType = sqlite3_column_type(ptrCompiledStatement, iCounter);
								try 
								{
									ColumnName = sqlite3_column_name(ptrCompiledStatement, iCounter);	
								} catch
								{
									ColumnName = "bad_" + iCounter.ToString();
								}

								switch(iType)
								{
									case (int)SQLITE_INTEGER:						
										objRow[ColumnName] = sqlite3_column_int(ptrCompiledStatement, iCounter);
										break;

									case (int)SQLITE_FLOAT:
										objRow[ColumnName] = sqlite3_column_int(ptrCompiledStatement, iCounter);
										break;

									case (int)SQLITE_TEXT:															
										objRow[ColumnName] = sqlite3_column_text(ptrCompiledStatement, iCounter);
										break;

									case (int)SQLITE_BLOB:																
										objRow[ColumnName] = sqlite3_column_text(ptrCompiledStatement, iCounter);														
										break;

									case (int)SQLITE_NULL:																
										objRow[ColumnName] = "";														
										break;									
								}
							}

							NewTable.Rows.Add(objRow);
						}

						return objDSet;
					}
				}
				return objDSet;
			}
			catch (Exception ex)
			{  
				Console.Write(ex.ToString());
				sqlite3_interrupt(MainDBHandle);
				return objDSet; 
			}
			finally
			{
				sqlite3_finalize(ptrCompiledStatement);
				LOCKED = false;
			}
		}
		#endregion
	}
}